package com.slipper.core.database.support;

import com.slipper.constants.Constants;
import com.slipper.core.database.IResolver;
import com.slipper.core.database.vo.FieldVO;
import com.slipper.core.database.vo.IndexFieldVO;
import com.slipper.core.database.vo.IndexVO;
import com.slipper.core.database.vo.TableVO;
import com.slipper.exception.CommonException;
import com.slipper.util.Tool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class PostgresqlResolver implements IResolver {
    private final static Logger logger = LoggerFactory.getLogger(PostgresqlResolver.class);

    private final static Map<String, String> dataTypeMap = new HashMap<>() {{
        put("smallint", "整数");
        put("integer", "整数");
        put("bigint", "长整数");
        put("real", "小数");
        put("double", "小数");
        put("numeric", "小数");
        put("money", "小数");
        put("date", "日期型");
        put("timestamp", "日期时间");
        put("timestamp with time zone", "日期时间");
        put("timestamp without time zone", "日期时间");
        put("character", "定长字符");
        put("character varying", "字符");
        put("varchar", "字符");
        put("boolean", "布尔");
        put("text", "大文本");
        put("bytea", "二进制");
    }};

    private final static Map<String, String> fieldTypeMap = new HashMap<>() {{
        put("字符", "varchar");
        put("整数", "integer");
        put("小数", "numeric");
        put("日期", "varchar");
        put("时间", "bigint");
        put("图片", "varchar");
        put("视频", "varchar");
        put("附件", "varchar");
        put("联动", "varchar");
        put("布尔", "boolean");
        put("大文本", "text");
        put("长整数", "bigint");
        put("二进制", "bytea");
        put("日期型", "date");
        put("日期时间", "timestamp");
        put("定长字符", "character");
    }};

    @Override
    public String tableTotalSQL(String tableName) {
        return "select count(*) " +
                "from information_schema.tables " +
                "where table_schema = current_schema() and lower(table_name) = '" + tableName + "'";
    }

    @Override
    public String tableListSQL(String tableName) {
        return "select " +
                "   lower(t.table_name) \"table_name\", " +
                "   obj_description(pgc.oid, 'pg_class') as \"table_comment\" " +
                "from information_schema.tables as t " +
                "    join pg_class as pgc on pgc.relname = t.table_name " +
                "    join pg_namespace as pgn on pgn.oid = pgc.relnamespace and pgn.nspname = t.table_schema " +
                "where t.table_catalog = current_database() " +
                "    and t.table_schema = current_schema() " +
                "    and t.table_type = 'BASE TABLE' " +
                "   " + (Tool.isNotBlank(tableName)
                        ? "and (lower(t.table_name) like '%" + tableName + "%' " +
                            "or obj_description(pgc.oid, 'pg_class') like '%" + tableName + "%')"
                        : "") +
                "order by t.table_name";
    }

    @Override
    public String tableColumnListSQL(String ...tableNames) {
        List<String> filterList = new ArrayList<>();
        for (String tableName : tableNames) {
            filterList.add(" lower(c.table_name) like '" + tableName + "' ");
        }
        return "select " +
                "    lower(c.table_name) \"table_name\", " +
                "    lower(c.column_name) \"column_name\", " +
                "    lower(c.data_type) \"column_type\", " +
                "    case when kcu.constraint_type is not null then '1' else '0' end \"column_pk\", " +
                "    case when c.is_nullable='YES' then '1' else '0' end \"column_null\", " +
                "    c.character_maximum_length \"column_length\", " +
                "    c.numeric_precision \"column_precision\", " +
                "    c.numeric_scale \"column_scale\", " +
                "    c.ordinal_position \"column_ordinal\" " +
                "from information_schema.columns c " +
                "    left join (" +
                "        select " +
                "            kcu.table_catalog, " +
                "            tc.constraint_type, " +
                "            kcu.table_schema, " +
                "            kcu.table_name, " +
                "            kcu.column_name  " +
                "        from information_schema.key_column_usage kcu, information_schema.table_constraints tc " +
                "        where kcu.table_catalog=tc.table_catalog " +
                "            and kcu.table_schema=tc.table_schema " +
                "            and kcu.table_name=tc.table_name " +
                "            and kcu.constraint_name = tc.constraint_name " +
                "            and tc.constraint_type = 'PRIMARY KEY') kcu on c.table_catalog=kcu.table_catalog " +
                "        and c.table_schema=kcu.table_schema " +
                "        and c.table_name=kcu.table_name " +
                "        and c.column_name=kcu.column_name " +
                "where c.table_catalog = current_database() and c.table_schema = current_schema() " +
                "    and (" + String.join(" or ", filterList) + ") " +
                "order by c.table_name, c.ordinal_position ";
    }

    @Override
    public String tableIndexSQL(String ...tableNames) {
        List<String> filterList = new ArrayList<>();
        for (String tableName : tableNames) {
            filterList.add(" lower(c.relname) like '" + tableName + "' ");
        }
        return "select " +
                "    lower(c.relname) as \"table_name\", " +
                "    lower(i.relname) as \"index_name\", " +
                "    lower(pg_get_indexdef(i.oid)) as \"index_def\" " +
                "from pg_index x " +
                "    join pg_class c on c.oid = x.indrelid " +
                "    join pg_class i on i.oid = x.indexrelid " +
                "    left join pg_namespace n on n.oid = c.relnamespace " +
                "    left join pg_tablespace t on t.oid = i.reltablespace " +
                "where c.relkind = any(array['r'::\"char\", 'm'::\"char\", 'p'::\"char\"]) " +
                "    and i.relkind = any(array['i'::\"char\", 'i'::\"char\"]) " +
                "    and n.nspname=current_schema() and x.indisprimary=false " +
                "    and (" + String.join(" or ", filterList) + ") ";
    }

    @Override
    public String columnSQL(String tableName) {
        return "select " +
                "    lower(c.column_name) \"name\", " +
                "    lower(c.data_type) \"type\", " +
                "    c.character_maximum_length \"length\", " +
                "    c.numeric_precision \"precision\", " +
                "    c.numeric_scale \"scale\", " +
                "    col_description(pgc.oid, a.attnum) AS \"comment\", " +
                "    case when kcu.constraint_type is not null then '1' else '0' end \"pk\" " +
                "from information_schema.columns c " +
                "    join pg_class as pgc on pgc.relname = c.table_name " +
                "    join pg_namespace as pgn on pgn.oid = pgc.relnamespace and pgn.nspname = c.table_schema " +
                "    join pg_attribute as a on a.attrelid = pgc.oid and a.attname = c.column_name " +
                "    left join (" +
                "        select " +
                "            kcu.table_catalog, " +
                "            tc.constraint_type, " +
                "            kcu.table_schema, " +
                "            kcu.table_name, " +
                "            kcu.column_name  " +
                "        from information_schema.key_column_usage kcu, information_schema.table_constraints tc " +
                "        where kcu.table_catalog=tc.table_catalog " +
                "            and kcu.table_schema=tc.table_schema " +
                "            and kcu.table_name=tc.table_name " +
                "            and kcu.constraint_name = tc.constraint_name " +
                "            and tc.constraint_type = 'PRIMARY KEY') kcu on c.table_catalog=kcu.table_catalog " +
                "        and c.table_schema=kcu.table_schema " +
                "        and c.table_name=kcu.table_name " +
                "        and c.column_name=kcu.column_name " +
                "where c.table_catalog = current_database() " +
                "   and c.table_schema = current_schema() " +
                "   and lower(c.table_name) = '" + tableName + "' " +
                "order by c.ordinal_position ";
    }

    @Override
    public String[] createTableDDL(TableVO table) {
        List<String> fieldList = new ArrayList<>();
        List<String> pkFieldList = new ArrayList<>();

        table.getFieldList().sort(Comparator.comparingInt(FieldVO::getOrdinal));
        for (FieldVO field : table.getFieldList()) {
            String fieldType = fieldTypeMap.get(field.getType());
            if (Tool.isBlank(fieldType)) throw new CommonException("不支持此数据类型【" + field.getType() + "】！");

            String fieldSql = wrapSymbol(field.getName()) + " " + fieldType;
            fieldSql += switch (field.getType()) {
                case "小数" -> "(" + field.getLength() + ", " + field.getPrecision() + ")";
                case "字符", "字符串", "日期" -> "(" + (field.getLength() == null ? 20 : field.getLength()) + ")";
                case "图片", "视频", "附件", "联动" -> "(20)";
                default -> "";
            };
            fieldSql += field.isNullable() ? "" : " not null ";
            fieldList.add(fieldSql);

            if (field.isPrimaryKey()) {
                pkFieldList.add(field.getName());
            }
        }

        String ddl = "create table " + wrapSymbol(table.getName()) + "(";
        ddl += String.join(", ", fieldList);
        if (!pkFieldList.isEmpty()) {
            ddl += ", primary key(";
            ddl += pkFieldList.stream().map(this::wrapSymbol).collect(Collectors.joining(", "));
            ddl += ")";
        }
        ddl += ")";

        List<String> resultList = new LinkedList<>();
        resultList.add(ddl);

        for (IndexVO index : table.getIndexList()) {
            String indexSql = "create ";
            if (index.isUnique()) {
                indexSql += " unique ";
            }
            indexSql += " index " + wrapSymbol(index.getName()) + " on " + wrapSymbol(table.getName());
            index.getFieldList().sort(Comparator.comparingInt(IndexFieldVO::getOrdinal));
            indexSql += "(";
            indexSql += index.getFieldList().stream()
                    .map(item -> wrapSymbol(item.getName()) + " " + (item.isAscending() ? "asc" : "desc"))
                    .collect(Collectors.joining(", "));
            indexSql += ")";
            resultList.add(indexSql);
        }
        return resultList.toArray(new String[0]);
    }

    @Override
    public String[] createOrUpdateViewDDL(String viewName, String sql) {
        return new String[] {"create or replace view " + wrapSymbol(viewName) + " as " + sql};
    }

    @Override
    public String dropTableDDL(String tableName) {
        return "drop table " + wrapSymbol(tableName);
    }

    @Override
    public String dropViewDDL(String viewName) {
        return "drop view " + wrapSymbol(viewName);
    }

    @Override
    public String addFieldDDL(String tableName, FieldVO field) {
        String fieldType = fieldTypeMap.get(field.getType());
        if (Tool.isBlank(fieldType)) throw new CommonException("不支持此数据类型【" + field.getType() + "】！");

        String fieldSql = "alter table " + wrapSymbol(tableName) + " add ";
        fieldSql += wrapSymbol(field.getName()) + " " + fieldType;
        fieldSql += switch (field.getType()) {
            case "小数" -> "(" + field.getLength() + ", " + field.getPrecision() + ")";
            case "字符", "字符串", "日期" -> "(" + (field.getLength() == null ? 20 : field.getLength()) + ")";
            case "图片", "视频", "附件", "联动" -> "(20)";
            default -> "";
        };
        fieldSql += field.isNullable() ? "" : " not null ";
        return fieldSql;
    }

    @Override
    public String[] modifyFieldDDL(String tableName, FieldVO newField, FieldVO oldField) {
        String newFieldType = fieldTypeMap.get(newField.getType());
        String oldFieldType = fieldTypeMap.get(oldField.getType() == null ? "unknown" : oldField.getType());
        if (Tool.isBlank(newFieldType)) throw new CommonException("不支持此数据类型【" + newField.getType() + "】！");

        List<String> ddlList = new LinkedList<>();
        if (!oldField.getName().equals(newField.getName())) {
            ddlList.add("alter table " + wrapSymbol(tableName)
                    + " rename column " + wrapSymbol(oldField.getName()) + " to "
                    + wrapSymbol(newField.getName()));
        }

        if (!newFieldType.equals(oldFieldType) || !Objects.equals(newField.getLength(), oldField.getLength())) {
            String fieldSql = "alter table " + wrapSymbol(tableName) + " alter column ";
            fieldSql += wrapSymbol(newField.getName()) + " type " + newFieldType;
            fieldSql += switch (newField.getType()) {
                case "小数" -> "(" + newField.getLength() + ", " + newField.getPrecision() + ")";
                case "字符", "字符串", "日期" -> "(" + (newField.getLength() == null ? 20 : newField.getLength()) + ")";
                case "图片", "视频", "附件", "联动" -> "(20)";
                default -> "";
            };
            fieldSql += " using " + wrapSymbol(newField.getName()) + "::" + newFieldType;
            ddlList.add(fieldSql);
        }

        String nullOrElseSql = "alter table " + wrapSymbol(tableName) + " alter column ";
        nullOrElseSql += wrapSymbol(newField.getName());
        nullOrElseSql += newField.isNullable() ? " drop not null " : " set not null ";
        ddlList.add(nullOrElseSql);

        return ddlList.toArray(new String[0]);
    }

    @Override
    public String addIndexDDL(String tableName, IndexVO index) {
        return "create " + (index.isUnique() ? "unique" : "") + " index "
                + wrapSymbol(index.getName()) + " on "
                + wrapSymbol(tableName)
                + "("
                + index.getFieldList().stream()
                .map(item -> wrapSymbol(item.getName()) + " " + (item.isAscending() ? "asc" : "desc"))
                .collect(Collectors.joining(", "))
                + ")";
    }

    @Override
    public String dropIndexDDL(String tableName, String indexName) {
        return "drop index if exists " + wrapSymbol(indexName);
    }

    @Override
    public void parseTable(Map<String, TableVO> tableMap, List<Map<String, Object>> fieldList) {
        fieldList.forEach(data -> {
            String tableName = (String) getValue("table_name", data);
            TableVO table = tableMap.get(tableName);
            if (table == null) {
                table = new TableVO();
                table.setName(tableName);
                tableMap.put(tableName, table);
            }

            FieldVO field = new FieldVO();
            field.setName((String) getValue("column_name", data));
            field.setType(changeType((String) getValue("column_type", data)));

            Object columnLength = getValue("column_length", data);
            Object columnPrecision = getValue("column_precision", data);
            Object columnScale = getValue("column_scale", data);

            Long length = null;
            Integer precision = null;
            if (columnLength != null) {
                length = Long.parseLong(columnLength.toString());
            } else if (columnPrecision != null) {
                length = Long.parseLong(columnPrecision.toString());
                if (columnScale != null) {
                    precision = Integer.parseInt(columnScale.toString());
                }
            }

            field.setLength(length);
            field.setPrecision(precision);
            field.setPrimaryKey((String) getValue("column_pk", data));
            field.setNullable((String) getValue("column_null", data));

            Object ordinal = getValue("column_ordinal", data);
            field.setOrdinal(ordinal == null ? 99 : Integer.parseInt(ordinal.toString()));
            table.addField(field);
        });
    }

    @Override
    public void parseIndex(Map<String, TableVO> tableMap, List<Map<String, Object>> indexList) {
        indexList.forEach(data -> {
            String tableName = (String) getValue("table_name", data);
            if (Tool.isBlank(tableName)) return;

            TableVO table = tableMap.get(tableName);
            if (table == null) {
                logger.warn("表结构不存在：{}", tableName);
                return;
            }

            String indexName = (String) getValue("index_name", data);
            if (Tool.isBlank(indexName)) return;

            IndexVO index = null;
            for (IndexVO temp : table.getIndexList()) {
                if (temp.getName().equalsIgnoreCase(indexName)) {
                    index = temp;
                    break;
                }
            }

            String indexDef = (String) getValue("index_def", data);
            if (Tool.isBlank(indexDef)) return;

            if (index == null) {
                index = new IndexVO();
                index.setName(indexName);
                index.setUnique(indexDef.toLowerCase().contains("unique") ? Constants.YES : Constants.NO);
                table.addIndex(index);
            }

            Pattern pattern = Pattern.compile("\\((.*?)\\)");
            Matcher matcher = pattern.matcher(indexDef);
            if(matcher.find()) {
                int i = 1;
                for (String item : matcher.group(1).split(",")) {
                    String[] fields = item.trim().split(" ");
                    if (fields.length == 0) return;

                    IndexFieldVO field = new IndexFieldVO();
                    field.setName(unwrapSymbol(fields[0]));
                    field.setOrderType(fields.length >= 2 && fields[1].equalsIgnoreCase("desc") ? Constants.ORDER_BY_DESC : Constants.ORDER_BY_ASC);
                    field.setOrdinal(i);
                    index.addField(field);
                    i++;
                }
            }
        });
    }

    public String changeType(String fieldType) {
        if (Tool.isBlank(fieldType)) throw new CommonException("未知数据库类型【" + fieldType + "】！");
        String dataType = dataTypeMap.get(fieldType.toLowerCase());
        if (Tool.isBlank(dataType)) throw new CommonException("未知数据库类型【" + fieldType + "】！");
        return dataType;
    }

    public boolean isEqualsType(String dataType, String columnType) {
        return fieldTypeMap.get(dataType) != null && fieldTypeMap.get(dataType).equalsIgnoreCase(columnType);
    }

    @Override
    public Map<String, String> getDataTypeMap() {
        return dataTypeMap;
    }

    public Object getValue(String key, Map<String, Object> data) {
        for (Map.Entry<String, Object> entry : data.entrySet()) {
            if (entry.getKey().equalsIgnoreCase(key)) {
                return entry.getValue();
            }
        }
        return null;
    }

    public String wrapSymbol(String value) {
        if (value.trim().startsWith("\"")) return value;
        return "\"" + value.trim().toLowerCase() + "\"";
    }

    public String unwrapSymbol(String value) {
        if (Tool.isBlank(value)) return value;

        if (value.trim().startsWith("\"")) {
            value = value.trim().substring(1);
        }
        if (value.trim().endsWith("\"")) {
            value = value.trim().substring(0, value.length() - 1);
        }
        return value;
    }
}
